package com.yss.reportworld.util;

import com.yss.common.core.constant.RequestDataHelper;
import com.yss.common.core.domain.ColumnInfo;
import com.yss.common.core.enums.TableVersionEnum;
import com.yss.common.core.enums.YssDataBaseEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.StringUtils;
import com.yss.common.core.utils.uuid.IdUtils;
import com.yss.common.security.utils.DictUtils;
import com.yss.reportworld.domain.Table;
import com.yss.reportworld.domain.TableDetail;
import com.yss.reportworld.domain.TableIndexed;
import com.yss.reportworld.enums.ReportWorldEnum;
import com.yss.system.api.domain.SysDictData;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import java.util.*;

@Slf4j
public class TableInfoUtil {
    private static String columnName = "ID,CREATE_PRSN,MDFY_PRSON,APPROVAL_STATUS,APPROVAL_NAME";

    /**
     * 设置版本号后缀拼接表名
     *
     * @param version
     */
    public static String getVersionSuffix(String version) {
        List<SysDictData> dictDatas = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isEmpty(dictDatas)) {
            throw new BusinessException("没有获取到版本号，请稍后重试！");
        }
        if (StringUtils.isEmpty(version) || version.equals(dictDatas.get(0).getDictValue())) {
            return "";
        }
        return StringUtils.getVersionSuffix(version); //表的版本号后缀
    }

    /**
     * 处理不同版本号使用不同表
     *
     * @param version
     */
    public static void handleTableVersion(String version) {
        // 最新版本设置版本号为空，直接取
        RequestDataHelper.removeThreadLocal();
        // 获取修改记录信息文件 取当前版本号的数据
        List<SysDictData> dictDatas = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isEmpty(dictDatas)) {
            throw new BusinessException("没有获取到版本号，请稍后重试！");
        }
        if (StringUtils.isEmpty(version) || version.equals(dictDatas.get(0).getDictValue())) {
            return;
        }
        String versionSuffix = StringUtils.getVersionSuffix(version);
        if (StringUtils.isEmpty(version)) {
            return;
        }
        RequestDataHelper.setRequestData(new HashMap<String, Object>() {{
            List<String> list = TableVersionEnum.getVersionTables();
            if (StringUtils.isNotEmpty(list)) {
                for (String tableName : list) {
                    if (StringUtils.isEmpty(tableName)) {
                        continue;
                    }
                    put(tableName, tableName + versionSuffix);
                    put(tableName.toLowerCase(), tableName.toLowerCase() + versionSuffix);
                }
            }
        }});
    }

    /**
     * 数据库的类型转成报表世界需要的表字段信息
     *
     * @param columnInfo
     * @return
     * @throws Exception
     */
    public static TableDetail getReportTableDetail(ColumnInfo columnInfo, String tableId) throws Exception {
        TableDetail tableDetail = new TableDetail();
        if (StringUtils.isNotEmpty(tableId)) {
            tableDetail.setId(IdUtils.fastSimpleUUID());
            tableDetail.setTableId(tableId);
        }
        tableDetail.setColumnName(columnInfo.getName());
        tableDetail.setColumnChineseName(columnInfo.getRemarks());
        tableDetail.setColumnNull("2".equals(columnInfo.getIsNull()) ? 2 : 1);
        tableDetail.setPrimaryKey(columnInfo.getPrimaryKey());
        if (StringUtils.isNotBlank(columnInfo.getDicInfo())) {
            tableDetail.setCodeName(columnInfo.getDicInfo());
        }
        tableDetail.setColumnDefault(columnInfo.getDefaultValue());
        if (StringUtils.oneCase(columnInfo.getDataTypeName(), "VARCHAR2,VARCHAR")) {
            tableDetail.setColumnType(1);
            tableDetail.setColumnLength(columnInfo.getPrecision());
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if ("NUMBER".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(2);
            tableDetail.setColumnLength(columnInfo.getPrecision());
            if (0 != columnInfo.getScale()) { //小数
                tableDetail.setColumnType(3);
                tableDetail.setColumnDecimal(columnInfo.getScale());
                // 针对 NUMBER（*）的
                if (0 == columnInfo.getPrecision() && -127 == columnInfo.getScale()) {
                    tableDetail.setColumnDecimal(0);
                    tableDetail.setColumnLength(0);
                    tableDetail.setColumnType(2);
                }
            }
        }
        if ("CLOB".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(4);
            tableDetail.setColumnLength(0);
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if (StringUtils.oneOf(columnInfo.getDataTypeName(), "TIMESTAMP")) {
            tableDetail.setColumnType(5);
            tableDetail.setColumnLength(columnInfo.getPrecision());
            // tableDetail.setColumnLength(columnInfo.getScale());
        }
        if ("DATE".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(6);
            tableDetail.setColumnLength(0);
        }
        if ("BLOB".equals(columnInfo.getDataTypeName())) { //BLOB
            tableDetail.setColumnType(7);
            tableDetail.setColumnLength(0);
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if ("NCLOB".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(8);
            tableDetail.setColumnLength(0);
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if ("NVARCHAR2".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(9);
            tableDetail.setColumnLength(columnInfo.getPrecision());
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if ("CHAR".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(10);
            tableDetail.setColumnLength(columnInfo.getPrecision());
            tableDetail.setMatcherChineseContent(1);//是否为中文
        }
        if ("INTEGER".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(11);
            tableDetail.setColumnLength(columnInfo.getPrecision());
        }
        if ("FLOAT".equals(columnInfo.getDataTypeName())) {
            tableDetail.setColumnType(12);
            tableDetail.setColumnLength(0);
        }
        // 特定字段的排除和主键字段的排除
        if (StringUtils.oneCase(columnInfo.getName().toUpperCase(), columnName) || 1 == columnInfo.getPrimaryKey()) {
            tableDetail.setMatcherChineseContent(2);//是否为中文
        }
        if (null == tableDetail.getColumnType()) {
            throw new BusinessException("系统不支持字段类型【" + columnInfo.getDataTypeName() + "】，请检查确认！");
        }
        return tableDetail;
    }

    /**
     * 通过字段类型返回
     *
     * @param tableDetail
     * @param length
     * @return
     */
    public static String handleCloum(TableDetail tableDetail, boolean length, String ftype) {
        YssDataBaseEnum baseEnum = YssDataBaseEnum.getEnum(ftype);
        switch (baseEnum) {
            case ORACLE:
                return handleCloum(tableDetail, true);
            case MYSQL:
                return handleCloumMysql(tableDetail, true);
            case DM:
                return handleCloum(tableDetail, true);
            case POSTGRESQL:
                return handleCloumMysql(tableDetail, true);
            default:
                return handleCloum(tableDetail, true);
        }
    }

    /**
     * 通过字段类型返回
     *
     * @param tableDetail
     * @param length
     * @return
     */
    public static String handleCloumMysql(TableDetail tableDetail, boolean length) {
        Integer columnType = tableDetail.getColumnType();
        switch (columnType) {
            case 1:
                if (tableDetail.getColumnLength() > 200 && tableDetail.getColumnLength() <= 256) {
                    return "TINYTEXT";
                }
                if (tableDetail.getColumnLength() >= 2000 && tableDetail.getColumnLength() <= 4000) {
                    return "TEXT";
                }
                return "VARCHAR" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 2:
                return "DECIMAL" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 3:
                return "DECIMAL" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + "," + tableDetail.getColumnDecimal() + ")" : "");
            case 4:
                return "TEXT";
            case 5:
                return "DATETIME" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 6:
                return "DATETIME" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 7:
                return "TEXT";
            case 8:
                return "TEXT";
            case 9:
                if (tableDetail.getColumnLength() > 200 && tableDetail.getColumnLength() <= 256) {
                    return "TINYTEXT";
                }
                if (tableDetail.getColumnLength() >= 2000 && tableDetail.getColumnLength() <= 4000) {
                    return "TEXT";
                }
                return "VARCHAR" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 10:
                if (tableDetail.getColumnLength() > 200 && tableDetail.getColumnLength() <= 256) {
                    return "TINYTEXT";
                }
                if (tableDetail.getColumnLength() >= 2000 && tableDetail.getColumnLength() <= 4000) {
                    return "TEXT";
                }
                return "VARCHAR" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 11:
                return "INTEGER" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 12:
                return "FLOAT";
            default:
                return null;
        }

    }

    /**
     * 通过字段类型返回
     *
     * @param tableDetail
     * @param length
     * @return
     */
    public static String handleCloum(TableDetail tableDetail, boolean length) {
        Integer columnType = tableDetail.getColumnType();
        switch (columnType) {
            case 1:
                return "VARCHAR2" + (length ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 2:
                return "NUMBER" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 3:
                return "NUMBER" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + "," + tableDetail.getColumnDecimal() + ")" : "");
            case 4:
                return "CLOB";
            case 5:
                return "TIMESTAMP" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 6:
                return "DATE" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 7:
                return "BLOB";
            case 8:
                return "NCLOB";
            case 9:
                return "NVARCHAR2" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 10:
                return "CHAR" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 11:
                return "INTEGER" + (length && StringUtils.isNotNull(tableDetail.getColumnLength()) && 0 != tableDetail.getColumnLength() ? "(" + tableDetail.getColumnLength() + ")" : "");
            case 12:
                return "FLOAT";
            default:
                return null;
        }

    }

    /**
     * 建表语句生成，区分数据库类型
     *
     * @param table
     * @param listDetail
     * @param ftype
     * @return
     * @throws Exception
     */
    public static String creatTable(Table table, List<TableDetail> listDetail, String ftype) {
        YssDataBaseEnum baseEnum = YssDataBaseEnum.getEnum(ftype);
        switch (baseEnum) {
            case ORACLE:
                return creatTableOracle(table, listDetail);
            case MYSQL:
                return creatTableMysql(table, listDetail);
            case DM:
                return creatTableOracle(table, listDetail);
            case POSTGRESQL:
                return creatTableMysql(table, listDetail);
            default:
                return creatTableOracle(table, listDetail);
        }
    }

    /**
     * 生成建表语句脚本 ORCLE
     *
     * @param table
     * @param listDetail
     * @return
     * @throws Exception
     */
    public static String creatTableOracle(Table table, List<TableDetail> listDetail) {
        StringBuffer buffer = new StringBuffer();
        StringBuffer primarykey = new StringBuffer();
        StringBuffer comment = new StringBuffer();
        StringBuffer indexed = new StringBuffer();
        buffer.append("\r\n").append("CREATE TABLE ").append(table.getName()).append("\r\n(");
        String chineseName = "";
        if (StringUtils.isNotBlank(table.getChineseName())) {
            chineseName = "comment on table " + table.getName() + "  is '" + table.getChineseName() + "';\r\n";
        }
        int i = 0;
        for (TableDetail detail : listDetail) {
            i++;
            if (StringUtils.isNull(detail) || StringUtils.isBlank(detail.getColumnName())) {
                continue;
            }
            buffer.append(detail.getColumnName()).append(" ").append(TableInfoUtil.handleCloum(detail, true));
            if (StringUtils.isNotBlank(detail.getColumnDefault())) {
                String defaultValue = getColumnDefault(detail.getColumnDefault(), YssDataBaseEnum.ORACLE.getCode());
                if (StringUtils.isNotBlank(detail.getColumnDefault())) {
                    buffer.append(" DEFAULT  ").append(defaultValue);
                }
            }
            if (2 == detail.getColumnNull()) {
                buffer.append("  NOT NULL");
            }
            //主键
            if (1 == detail.getPrimaryKey()) {
                primarykey.append(detail.getColumnName()).append(",");
            }
            if (i == listDetail.size() && primarykey.length() < 2) {  //最后一条，表又没有主键的时候，不拼逗号
                buffer.append("\r\n");
            } else {
                buffer.append(",").append("\r\n");
            }
            // 字段描述
            if (StringUtils.isNotBlank(detail.getColumnChineseName())) {
                comment.append(" COMMENT ON COLUMN " + table.getName()).append(".")
                        .append(detail.getColumnName()).append(" IS ")
                        .append("'" + detail.getColumnChineseName() + "';")
                        .append("\r\n");
            }
            // 索引
         /*   if (1 == detail.getIndexed()) {
                if (indexedMap.containsKey(detail.getIndexName())) {
                    List<String> list = indexedMap.get(detail.getIndexName());
                    list.add(detail.getIndexColumnName());
                    indexedMap.put(detail.getIndexName(), list);
                } else {
                    List<String> list = new ArrayList<>();
                    list.add(detail.getIndexColumnName());
                    indexedMap.put(detail.getIndexName(), list);
                }
            }*/
        }
        if (primarykey.length() > 2) {
            buffer.append(" CONSTRAINT ")
                    .append(table.getName())
                    .append(" PRIMARY KEY (" + primarykey.deleteCharAt(primarykey.length() - 1) + ")")
                    .append("\r\n");
        }

        // 索引
        List<TableIndexed> indexedList = table.getListIndexed();
        if (!CollectionUtils.isEmpty(indexedList)) {
            Map<String, List<String>> indexedMap = new HashMap<>();
            for (TableIndexed tableIndexed : indexedList) {
                if (StringUtils.isNull(tableIndexed)) {
                    continue;
                }
                List<String> list = null;
                if (indexedMap.containsKey(tableIndexed.getIndexedName())) {
                    list = indexedMap.get(tableIndexed.getIndexedName());
                    list.add(tableIndexed.getIndexedColumnName());
                } else {
                    list = new ArrayList<>();
                    list.add(tableIndexed.getIndexedColumnName());
                }
                indexedMap.put(tableIndexed.getIndexedName(), list);
            }
            if (!CollectionUtils.isEmpty(indexedMap)) {
                for (Map.Entry<String, List<String>> entry : indexedMap.entrySet()) {
                    if (StringUtils.isBlank(entry.getKey())) {
                        continue;
                    }
                    List<String> list = entry.getValue();
                    if (CollectionUtils.isEmpty(list)) {
                        continue;
                    }
                    indexed.append(" CREATE INDEX " + entry.getKey()).append(" ON ").append(table.getName()).append("(");
                    StringBuffer indexedCol = new StringBuffer();
                    for (String columnName : list) {
                        indexedCol.append(columnName).append(",");
                    }
                    indexed.append(indexedCol.deleteCharAt(indexedCol.length() - 1)).append(")").append(";").append("\r\n");
                }
            }
        }
        buffer.append(");").append("\r\n");
        buffer.append(chineseName).append(comment).append(indexed);

        return buffer.toString();
    }


    /**
     * 生成建表语句脚本 MYSQL
     *
     * @param table
     * @param listDetail
     * @return
     * @throws Exception
     */
    public static String creatTableMysql(Table table, List<TableDetail> listDetail) {
        StringBuffer buffer = new StringBuffer();
        StringBuffer primarykey = new StringBuffer();
        StringBuffer indexed = new StringBuffer();
        // buffer.append("\r\n").append("DROP TABLE ").append(table.getName()).append(";\r\n");
        buffer.append("\r\n").append("CREATE TABLE ").append(table.getName()).append("\r\n(");
        String chineseName = "";
        if (StringUtils.isNotBlank(table.getChineseName())) {
            chineseName = "comment='" + table.getChineseName() + "';\r\n";
        }
        int i = 0;
        for (TableDetail detail : listDetail) {
            i++;
            if (StringUtils.isNull(detail) || StringUtils.isBlank(detail.getColumnName())) {
                continue;
            }
            buffer.append(detail.getColumnName()).append(" ").append(TableInfoUtil.handleCloum(detail, true, YssDataBaseEnum.MYSQL.getCode()));
            if (StringUtils.isNotBlank(detail.getColumnDefault())) {
                String defaultValue = getColumnDefault(detail.getColumnDefault(), YssDataBaseEnum.MYSQL.getCode());
                if (StringUtils.isNotBlank(defaultValue)) {
                    buffer.append(" DEFAULT  ").append(defaultValue);
                }
            }
            if (2 == detail.getColumnNull()) {
                buffer.append("  NOT NULL");
            }
            if (StringUtils.isNotBlank(detail.getColumnChineseName())) {
                buffer.append("  COMMENT ").append("'").append(detail.getColumnChineseName()).append("'");
            }
            //主键
            if (1 == detail.getPrimaryKey()) {
                primarykey.append(detail.getColumnName()).append(",");
            }
            if (i == listDetail.size() && primarykey.length() < 2) {  //最后一条，表又没有主键的时候，不拼逗号
                buffer.append("\r\n");
            } else {
                buffer.append(",").append("\r\n");
            }
        }
        if (primarykey.length() > 2) {
            buffer.append(" CONSTRAINT ")
                    .append(table.getName())
                    .append(" PRIMARY KEY (" + primarykey.deleteCharAt(primarykey.length() - 1) + ")")
                    .append("\r\n");
        }

        // 索引
        List<TableIndexed> indexedList = table.getListIndexed();
        if (!CollectionUtils.isEmpty(indexedList)) {
            Map<String, List<String>> indexedMap = new HashMap<>();
            for (TableIndexed tableIndexed : indexedList) {
                if (StringUtils.isNull(tableIndexed)) {
                    continue;
                }
                List<String> list = null;
                if (indexedMap.containsKey(tableIndexed.getIndexedName())) {
                    list = indexedMap.get(tableIndexed.getIndexedName());
                    list.add(tableIndexed.getIndexedColumnName());
                } else {
                    list = new ArrayList<>();
                    list.add(tableIndexed.getIndexedColumnName());
                }
                indexedMap.put(tableIndexed.getIndexedName(), list);
            }
            if (!CollectionUtils.isEmpty(indexedMap)) {
                for (Map.Entry<String, List<String>> entry : indexedMap.entrySet()) {
                    if (StringUtils.isBlank(entry.getKey())) {
                        continue;
                    }
                    List<String> list = entry.getValue();
                    if (CollectionUtils.isEmpty(list)) {
                        continue;
                    }
                    indexed.append(" CREATE INDEX " + entry.getKey()).append(" ON ").append(table.getName()).append("(");
                    StringBuffer indexedCol = new StringBuffer();
                    for (String columnName : list) {
                        indexedCol.append(columnName).append(",");
                    }
                    indexed.append(indexedCol.deleteCharAt(indexedCol.length() - 1)).append(")").append(";").append("\r\n");
                }
            }
        }
        buffer.append(")").append(chineseName).append(indexed);

        return buffer.toString();
    }

    /**
     * 获取字段的默认值
     *
     * @return
     */
    public static String getColumnDefault(String columnDefault, String ftype) {
        if (StringUtils.isBlank(columnDefault) || StringUtils.isBlank(ftype)) {
            return null;
        }
        if (StringUtils.isEmpty(columnDefault)) {
            return null;
        }
        YssDataBaseEnum baseEnum = YssDataBaseEnum.getEnum(ftype);
        switch (baseEnum) {
            case ORACLE:
                return columnDefault;
            case MYSQL:
                // oracle的特殊默认值
                if (StringUtils.oneOf(columnDefault.toUpperCase(), "SYS_GUID")) {
                    // return "UUID()"; //低版本不支持
                    return null;
                }
                if (StringUtils.oneOf(columnDefault.toUpperCase(), "TO_CHAR")) {
                    return null;
                }
                if (StringUtils.oneOf(columnDefault.toUpperCase(), "SYSDATE")) {
                    return "CURRENT_TIMESTAMP";
                }
                if (StringUtils.oneOf(columnDefault.toUpperCase(), "TO_DATE,TO_TIMESTAMP")) {
                    return null;
                }
            case DM:
                return columnDefault;
            case POSTGRESQL:
                return columnDefault;
            default:
                return columnDefault;
        }
    }
}
